package com.tool;

import cn.com.tiza.tstar.common.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2018-4-18.
 */
public class db {
    //数据库连接对象
    private static Connection conn = null;

    private static String driver = "oracle.jdbc.driver.OracleDriver"; //驱动
//    private static String driver = "com.mysql.jdbc.Driver"; //驱动

    private static String url = "jdbc:oracle:thin:@10.80.0.81:1521/xgdb"; //连接字符串
//    private static String url = "jdbc:mysql://10.100.2.77:3306/roadhis"; //连接字符串

    private static String username = "XIOT"; //用户名

    private static String password = "XIOT"; //密码


    // 获得连接对象
    private static synchronized Connection getConn(){
        if(conn == null){
            try {
                Class.forName(driver);
                conn = DriverManager.getConnection(url, username, password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return conn;
    }

    //执行查询语句
    public void query(String sql, boolean isSelect) throws SQLException{
        PreparedStatement pstmt;

        try {
            pstmt = getConn().prepareStatement(sql);
            //建立一个结果集，用来保存查询出来的结果
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                String name = rs.getString("terminal_id");
                System.out.println(name);
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //执行查询语句
    public List<String> queryID(String sql, boolean isSelect) throws SQLException{
        PreparedStatement pstmt;
        List<String> idList=new ArrayList<>();
        try {
            pstmt = getConn().prepareStatement(sql);
            //建立一个结果集，用来保存查询出来的结果
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                String name = rs.getString("terminal_id");
                System.out.println(name);
                idList.add(name);
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  idList;
    }
    //执行查询语句
    public List<String> querydeviceID(String sql, boolean isSelect) throws SQLException{
        PreparedStatement pstmt;
        List<String> idList=new ArrayList<>();
        try {
            pstmt = getConn().prepareStatement(sql);
            //建立一个结果集，用来保存查询出来的结果
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                String name = rs.getString("ID");
//                String protocal=rs.getString("protocolid");
//                System.out.println(name);
                idList.add(name);
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  idList;
    }
    public boolean insertdata(String sql) throws SQLException{
        PreparedStatement pstmt;
        List<String> idList=new ArrayList<>();
        try {
            pstmt = getConn().prepareStatement(sql);
            //建立一个结果集，用来保存查询出来的结果
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return  true;
    }
    public void query(String sql) throws SQLException{
        PreparedStatement pstmt;
        pstmt = getConn().prepareStatement(sql);
        pstmt.execute();
        pstmt.close();
    }
    //执行查询语句
    public List<String> querycmd(String sql, boolean isSelect) throws SQLException{
        PreparedStatement pstmt;
        List<String> idList=new ArrayList<>();
        try {
            pstmt = getConn().prepareStatement(sql);
            //建立一个结果集，用来保存查询出来的结果
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                String name = rs.getString("content");
                System.out.println(name);
                idList.add(name);
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  idList;
    }
    public int insertmileage(Date workdate,double mile,double totalmile,String vid,int year,int month) {
        String y=String.valueOf(year);
        String m=month>9?String.valueOf(month):"0"+String.valueOf(month);
       int VehicleId= Integer.parseInt(vid);
        PreparedStatement ps = null;
        ResultSet rs = null;
        int updateNum = 0;
//        String updateSql = "insert into STS_MILEAGE_2019_02" +
//                " (MILEAGE , TOTALMILEAGE,TYPE,VEHICLEID,DATETIME,CREATETIME)VALUES(?,?,?,?,?,sysdate)";

        String updateSql="MERGE INTO STS_MILEAGE_"+y+"_"+m+" t1 USING (SELECT ? VEHICLEID,? DATETIME,? \"TYPE\",? MILEAGE,? TOTALMILEAGE,SYSDATE CREATETIME\n" +
                "FROM dual\n" +
                ") t2 ON (\n" +
                "  t1.VEHICLEID = t2.VEHICLEID and\n" +
                "  t1.DATETIME = t2.DATETIME\n" +
                ")\n" +
                "WHEN MATCHED THEN\n" +
                "  UPDATE\n" +
                "SET t1.MILEAGE = t2.MILEAGE,\n" +
                " t1.TOTALMILEAGE = t2.TOTALMILEAGE,\n" +
                " t1.CREATETIME = t2.CREATETIME\n" +
                "WHEN NOT MATCHED THEN\n" +
                " INSERT\n" +
                "  (t1.MILEAGE,\n" +
                " \t t1.TOTALMILEAGE,\n" +
                " \t t1.\"TYPE\",\n" +
                " \t t1.VEHICLEID,\n" +
                " \t t1.DATETIME,\n" +
                "   t1.CREATETIME)\n" +
                "VALUES\n" +
                "\t(\n" +
                "    t2.MILEAGE,\n" +
                "    t2.TOTALMILEAGE,\n" +
                "    t2.\"TYPE\",\n" +
                "    t2.VEHICLEID,\n" +
                "    t2.DATETIME,\n" +
                "    t2.CREATETIME\n" +
                "\t)\n";
        try {
            ps = getConn().prepareStatement(updateSql);
            ps.setInt(1, VehicleId);
            ps.setDate(2, workdate);
            ps.setInt(3, 4);
            ps.setDouble(4,mile);
            ps.setDouble(5, totalmile);
            updateNum = ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("异常信息："+e.getMessage());
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
        return updateNum;
    }
    //关闭连接
    public void close(){
        try {
            getConn().close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public int insertoile(Date workdate,double mile,double totalmile,String vid,int year,int month) {
        String y=String.valueOf(year);
        String m=month>9?String.valueOf(month):"0"+String.valueOf(month);
        int VehicleId= Integer.parseInt(vid);
        PreparedStatement ps = null;
        ResultSet rs = null;
        int updateNum = 0;
//        String updateSql = "insert into STS_MILEAGE_2019_02" +
//                " (MILEAGE , TOTALMILEAGE,TYPE,VEHICLEID,DATETIME,CREATETIME)VALUES(?,?,?,?,?,sysdate)";

        String updateSql="MERGE INTO STS_OIL_"+y+"_"+m+" t1 USING (SELECT ? VEHICLEID,? DATETIME,? \"PROTOCOL\",? OIL,? TOTALOIL,SYSDATE CREATETIME\n" +
                "FROM dual\n" +
                ") t2 ON (\n" +
                "  t1.VEHICLEID = t2.VEHICLEID and\n" +
                "  t1.DATETIME = t2.DATETIME\n" +
                ")\n" +
                "WHEN MATCHED THEN\n" +
                "  UPDATE\n" +
                "SET t1.OIL = t2.OIL,\n" +
                " t1.TOTALOIL = t2.TOTALOIL,\n" +
                " t1.CREATETIME = t2.CREATETIME\n" +
                "WHEN NOT MATCHED THEN\n" +
                " INSERT\n" +
                "  (t1.OIL,\n" +
                " \t t1.TOTALOIL,\n" +
                " \t t1.\"PROTOCOL\",\n" +
                " \t t1.VEHICLEID,\n" +
                " \t t1.DATETIME,\n" +
                "   t1.CREATETIME)\n" +
                "VALUES\n" +
                "\t(\n" +
                "    t2.OIL,\n" +
                "    t2.TOTALOIL,\n" +
                "    t2.\"PROTOCOL\",\n" +
                "    t2.VEHICLEID,\n" +
                "    t2.DATETIME,\n" +
                "    t2.CREATETIME\n" +
                "\t)\n";
        try {
            ps = getConn().prepareStatement(updateSql);
            ps.setInt(1, VehicleId);
            ps.setDate(2, workdate);
            ps.setInt(3, 4);
            ps.setDouble(4,mile);
            ps.setDouble(5, totalmile);
            updateNum = ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("异常信息："+e.getMessage());
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
        return updateNum;
    }
}
